package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Score;
import util.ConnectionUtil;

public class ScoreDao {

	public static void add(int stu_id, int course_id, int score) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "INSERT INTO grade (StudentID, CoureseID, Grade) VALUES(?,?,?)";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, stu_id);
			ps.setInt(2, course_id);
			ps.setInt(3, score);
			int result = ps.executeUpdate();
			if (result > 0) {
				System.out.println("选课成功");
			} else {
				System.out.println("选课失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static ArrayList<Score> getScoreList(int studentid) {
		ArrayList<Score> scList = new ArrayList<>();
		Connection conn = ConnectionUtil.getConnection();
		String sql = "SELECT Name,Course,Grade FROM student,course,grade WHERE student.StudentID = ? AND student.StudentID = grade.StudentID AND student.CoureseID = course.CoureseID;";
		try {
			PreparedStatement pt = conn.prepareStatement(sql);
			pt.setInt(1, studentid);
			ResultSet rs = pt.executeQuery();

			while (rs.next()) {
				String stuName = rs.getString(1);
				String courseName = rs.getString(2);
				int ScoreNum = rs.getInt(3);

				Score score = new Score(stuName, courseName, ScoreNum);
				scList.add(score);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scList;
	}

}
